﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
using System.Data.OleDb;
using BHXH_NT.Properties;
using BHXH_NT.Data;
using System.Data.Common;
using System.Configuration;
using C1.Win.C1FlexGrid;

namespace BHXH_NT
{
    public partial class frmBCCT : Form
    {
        private string connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
        public frmBCCT()
        {
            InitializeComponent();
        }
        #region "Method Private"
        //
        //lay danh sach tien to so bhxh duoc cap trong nam hien tai
        private String ArrayBhxh()
        {
            string strSobhxh = null;
            string queryString = "select LEFT(TUSO,2) AS TUSO ,LEFT(DENSO,2) AS DENSO,RIGHT(NAM,2) AS NAM "
           + " from dbo.DMSOBHXH WHERE NAM >= " + System.Convert.ToInt16(txtNam.Text) + " AND LOAI = 'A'";
            using (SqlConnection connection =
                      new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    for (int i = Convert.ToInt16(reader["TUSO"]); i <= Convert.ToInt16(reader["DENSO"]); i++)
                    {
                        strSobhxh = strSobhxh + "'" + Properties.Settings.Default.MaTinh + reader["NAM"].ToString() + i.ToString() + "',";
                    }

                }
            }

            strSobhxh = strSobhxh.Substring(0, strSobhxh.Length - 1);
            return strSobhxh;
        }
        //
        //kiem tra dieu kien truoc khi in mau 09
        private bool Check09SBH()
        {
            if (txtTT.Text == "")
            {
                MessageBox.Show("Nhập tiền tố");
                txtTT.Focus();
                return false;
            }
            if (txttu.Text == "")
            {
                MessageBox.Show("Nhập mã đơn vị");
                txttu.Focus();
                return false;
            }
            if (txtden.Text == "")
            {
                MessageBox.Show("Nhập mã đơn vị");
                txtden.Focus();
                return false;
            }
            if (txtNam.Text == "")
            {
                MessageBox.Show("Nhập Năm");
                txtNam.Focus();
                return false;
            }
            if (cboQuy.Text == "")
            {
                MessageBox.Show("Nhập kỳ");
                txtNam.Focus();
                return false;
            }
            if (System.Convert.ToInt16(txttu.Text) > System.Convert.ToInt16(txtden.Text))
            {
                MessageBox.Show("Mã đơn vị từ phải nhỏ hơn mã đơn vị đến");
                txttu.Focus();
                return false;
            }
            return true;
        }
        //
        //kiem tra dieu kien truoc khi in mau 10
        private bool Check10SBH()
        {
            if (txtTT.Text == "")
            {
                MessageBox.Show("Nhập tiền tố");
                txtTT.Focus();
                return false;
            }

            if (txtNam.Text == "")
            {
                MessageBox.Show("Nhập Năm");
                txtNam.Focus();
                return false;
            }
            if (cboQuy.Text == "")
            {
                MessageBox.Show("Nhập kỳ");
                txtNam.Focus();
                return false;
            }
            return true;
        }
        #endregion

        #region "Method Event"
        private void frmBCCT_Load(object sender, EventArgs e)
        {
            cboQuy.SelectedIndex = 0;
            txtTT.Text = "TA";
            txtNam.Text = DateTime.Now.Year.ToString();

        }
        //
        //in mau 09
        private void cmdIn_Click(object sender, EventArgs e)
        {
            if (Check09SBH() == false) { return; }
            int dkthamgia = 0, dkcapso = 0;
            string strNamTruoc = System.Convert.ToInt16(txtNam.Text) - 1 + "12";//lay nam truoc nam hien tai
            string strNamthang = txtNam.Text + "01";//thang dau nam hien tai
            string strNamthang1 = txtNam.Text + "/01";//kieu format khac thang dau nam hien tai
            string strNam = txtNam.Text;// nam hien tai
            string strMaDvi, queryString;
            string strSobhxh = null;
            int k, x = 0, intFrom, intTo, intThang = 0;
            bcctbhxh dsBCCT = new bcctbhxh();// khoi tao dataset 
            intThang = (cboQuy.SelectedIndex + 1) * 3; // lay thang cuoi cung cua quy
            intFrom = System.Convert.ToInt16(txttu.Text);//ma don vi bat dau
            intTo = System.Convert.ToInt16(txtden.Text);//ma don vi ket thuc
            strSobhxh = ArrayBhxh(); //lay danh sach so bhxh duoc cap trong nam
            Cursor.Current = Cursors.WaitCursor;
            for (k = intFrom; k <= intTo; k++) //duyet tung don vi
            {
                strMaDvi = txtTT.Text + String.Format("{0:0000}", k) + "A"; //ghep thanh ma don vi
                //lay thang dau tien trong nam
                queryString = "SELECT a.Madvi,b.TenDvi,SUM(dauky) as dauky,sum(daukycapso) as daukycapso,"
                + " sum(tangthamgia) as tangthamgia ,sum(tangcapso) as tangcapso,sum(giamthamgia) as giamthamgia,"
                + " SUM(QT) AS QT, SUM(BL) as BL,SUM(HT) as HT,SUM(TT) as TT,SUM(BL) + SUM(HT) + SUM(TT) AS TONGTRGIAM, SUM(BL) + SUM(HT) + SUM(TT) + SUM(QT) AS TONGTR, SUM(TRCL) as TRCL,"
                + " SUM(TONGBIA) AS TONGBIA, sum(BIATRONGKY) as BIATRONGKY,SUM(TONGBIA) - sum(BIATRONGKY) as BIAKYTRUOC,SUM(BIACAPLAI) AS BIACAPLAI,"
                + " SUM(dauky) + sum(tangthamgia) - sum(giamthamgia) AS CKTHAMGIA, "
                + " sum(daukycapso) + sum(tangcapso) - sum(giamcapso) + SUM(TongBiaTang) AS CKCAPSO "
                + " FROM"
                + " (SELECT b.Madvi, count(a.Sobhxh) as dauky, " //lay so luong dau ky tham gia va dau ky tham gia cap so
                + " sum(case when left(a.Sobhxh,2) = '" + Settings.Default.MaTam + "' or left(a.sobhxh,6) in (" + strSobhxh + ") then  0 else 1 end)  as daukycapso,"
                + " '' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso, '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY"
                + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
                + " (SELECT Sobhxh, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE Madvi='" + strMaDvi + "' "
                + " AND Loaidt in ('ML','HS') AND Ma_nte='VND' AND LEFT(Maqt,6)=Denthang "
                + " AND LEFT(Maqt,6)<=" + strNamTruoc + " AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY Sobhxh) a  "
                + " WHERE a.Sobhxh=b.Sobhxh AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
                + " AND b.Madvi='" + strMaDvi + "'  AND b.Loaidt IN ('ML','HS')  AND b.Ma_nte='VND'  "
                + " AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Ml+b.Mlyt<>0 AND "
                + " CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 group by b.Madvi"
                + " union all" //lay so luong tang tham gia trong ky, giam tham gia trong ky
                + " SELECT a.Madvi,'' as dauky, '' as daukycapso,"
                + " ISNULL(sum(case when b.Pa in ('ON','TM','TC','TD') then 1 else 0 end),0) as tangthamgia,"
                + " ''  as tangcapso,"
                + " ISNULL(sum(case when b.Pa in ('GH','TS','OF','GD','GC') then 1 else 0 end),0) as giamthamgia,"
                + " '' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY"
                + " FROM HSC47_Hdr a, HSC47_Dtl b "
                + " WHERE a.KeySL= b.Keysl AND a.Madvi='" + strMaDvi + "'  "
                + " AND a.Ma_Nte='VND'  AND LEFT(a.Maqt,6)=" + strNamthang + " AND  DENTHANG = " + strNamthang
                + " group by a.Madvi "
                + " union all " // tang tham gia cap so, giam tham gia cap so
                + " SELECT a.Madvi,'' as dauky, '' as daukycapso, '' as tangthamgia,  "
                + " ISNULL(sum(case when b.Pa in ('ON','TM','TC','TD') then 1 else 0 end),0) as tangcapso, '' as giamthamgia,  "
                + " ISNULL(sum(case when b.Pa in ('GH','TS','OF','GD','GC') then 1 else 0 end),0) as giamcapso, "
                + " '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY  "
                + " FROM HSC47_Hdr a, HSC47_Dtl b  WHERE a.KeySL= b.Keysl AND a.Madvi='" + strMaDvi + "'"
                + " AND a.Ma_Nte='VND'  AND LEFT(a.Maqt,6)= " + strNamthang + " AND  DENTHANG =  " + strNamthang
                + " AND ((left(b.Sobhxh,2) <> '" + Settings.Default.MaTam + "' and left(b.sobhxh,6) not in (" + strSobhxh + "))  "
                + " or left(b.sobhxh,6) in (" + strSobhxh + ") and b.Sobhxh in ( "
                + " select sobhxh from " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL where ngaycap < a.ngayct and madvi = a.madvi)) "
                + " group by a.Madvi "
                + " union all " // so luong in to roi  QT,BL,HT,TT va cap lai
                + " SELECT Intr_hdr.Madvi,'' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,"
                + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN in ( 'QT','' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as QT,"
                + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'BL' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as BL,"
                + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'HT' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as HT,"
                + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'TU' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as TT,"
                + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAI =  '2' then 1 else 0 end),0) as TRCL,"
                + " '' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY"
                + " FROM  " + Settings.Default.DBName_Sothe + ".dbo.INTR_HDR Intr_hdr WHERE  Intr_hdr.MADVI =  '" + strMaDvi + "'   AND  "
                + " LEFT(CONVERT(nvarchar(20), Intr_hdr.NGAYCAP, 111),7) = '" + strNamthang1 + "'"
                + " group by Intr_hdr.Madvi "
                + " UNION ALL" //so luong bia, bia cap lai
                + " SELECT Inso_dtl.Madvi,'' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,"
                + " sum(case when Inso_dtl.LOAI = '1' then 1 else 0 end) AS TONGBIA,'' as TongBiaTang,"
                + " sum(case when Inso_dtl.LOAI = '2' then 1 else 0 end) AS BIACAPLAI,"
                + " '' as BIATRONGKY"
                + " FROM " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL Inso_dtl "
                + " WHERE LEFT(CONVERT(nvarchar(20), Inso_dtl.NGAYCAP, 111),7) = '" + strNamthang1 + "'"
                + " AND Inso_dtl.MADVI = '" + strMaDvi + "'   "
                + " group by Inso_dtl.Madvi "
                + " union all" // tong bia tang 
                + " select Inso_dtl.Madvi,'' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,"
                + " '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL, "
                + " '' AS TONGBIA,count(sobhxh) as TongBiaTang ,'' AS BIACAPLAI, '' as BIATRONGKY FROM "
                + " MISBHXH_SOTHE_NT.dbo.INSO_DTL Inso_dtl    "
                + " WHERE LEFT(CONVERT(nvarchar(20), Inso_dtl.NGAYCAP, 111),7) = '" + strNamthang1 + "' and Inso_dtl.madvi = '" + strMaDvi + "' and Inso_dtl.LOAI = '1' "
                + " and sobhxh in ("
                + " select d.sobhxh FROM HSC47_Hdr c, HSC47_Dtl d where"
                + " c.KeySL= d.Keysl AND c.Ma_Nte='VND' and c.madvi = Inso_dtl.madvi and"
                + " d.pa not in ('GH','TS','OF','GD','GC','TT') and sobhxh = d.sobhxh and"
                + " c.Maqt in("
                + " select max(a.Maqt) FROM HSC47_Hdr a, HSC47_Dtl b "
                + " WHERE a.KeySL= b.Keysl and b.sobhxh = d.sobhxh  and a.ngayct < ngaycap AND b.DENTHANG = LEFT(a.Maqt,6)"
                + " )) group by Inso_dtl.Madvi"
                + " union all "//tong bia tang trong ky
                + " select a.Madvi, '' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' as TONGBIA,'' as TongBiaTang,'' as BIACAPLAI,"
                + " count(b.sobhxh) as BIATRONGKY FROM HSC47_Hdr a, HSC47_Dtl b, " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL c "
                + " WHERE a.KeySL= b.Keysl and b.sobhxh = c.sobhxh AND a.Madvi='" + strMaDvi + "'  "
                + " AND a.Ma_Nte='VND' and b.Pa in ('ON','TM','TC','TD') AND LEFT(a.Maqt,6)= " + strNamthang + " and LEFT(CONVERT(nvarchar(20), c.NGAYCAP, 111),7) = '" + strNamthang1 + "'"
                + " group by a.Madvi ) a , Dmdonvi b where a.Madvi = b.Madvi "
                + " group by a.Madvi,b.TenDvi";


                using (SqlConnection connection =
                       new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = queryString;
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        DataRow newRow = dsBCCT.Tables[0].NewRow();
                        newRow["Madvi"] = strMaDvi;
                        newRow["TenDvi"] = reader["TenDvi"];
                        newRow["thang"] = "tháng 1";
                        newRow["dauky"] = reader["dauky"];
                        newRow["daukycapso"] = reader["daukycapso"];
                        newRow["tangthamgia"] = reader["tangthamgia"];
                        newRow["tangcapso"] = reader["tangcapso"];
                        newRow["giamthamgia"] = reader["giamthamgia"];
                        newRow["QT"] = reader["QT"];
                        newRow["BL"] = reader["BL"];
                        newRow["HT"] = reader["HT"];
                        newRow["TT"] = reader["TT"];
                        newRow["TONGTRGIAM"] = reader["TONGTRGIAM"];
                        newRow["TONGTR"] = reader["TONGTR"];
                        newRow["TRCL"] = reader["TRCL"];
                        newRow["TONGBIA"] = reader["TONGBIA"];
                        newRow["BIATRONGKY"] = reader["BIATRONGKY"];
                        newRow["BIAKYTRUOC"] = reader["BIAKYTRUOC"];
                        newRow["BIACAPLAI"] = reader["BIACAPLAI"];
                        newRow["ckthamgia"] = reader["ckthamgia"];
                        newRow["ckcapso"] = reader["ckcapso"];
                        dkthamgia = (int)reader["ckthamgia"];
                        dkcapso = (int)reader["ckcapso"];
                        dsBCCT.Tables[0].Rows.Add(newRow);
                    }
                    reader.Close();
                }

                for (int i = 2; i <= intThang; i++) //duyet tu thang 2 den thang cuoi cung trong quy
                {
                    queryString = "SELECT SUM(dauky) as dauky,sum(daukycapso) as daukycapso,"
                    + " sum(tangthamgia) as tangthamgia ,sum(tangcapso) as tangcapso,sum(giamthamgia) as giamthamgia,"
                    + " SUM(QT) AS QT, SUM(BL) as BL,SUM(HT) as HT,SUM(TT) as TT,SUM(BL) + SUM(HT) + SUM(TT) AS TONGTRGIAM, SUM(BL) + SUM(HT) + SUM(TT) + SUM(QT) AS TONGTR, SUM(TRCL) as TRCL,"
                    + " SUM(TONGBIA) AS TONGBIA, sum(BIATRONGKY) as BIATRONGKY,SUM(TONGBIA) - sum(BIATRONGKY) as BIAKYTRUOC,SUM(BIACAPLAI) AS BIACAPLAI,"
                    + " SUM(dauky) + sum(tangthamgia) - sum(giamthamgia) AS CKTHAMGIA, "
                    + " sum(daukycapso) + sum(tangcapso) - sum(giamcapso) + SUM(TongBiaTang) AS CKCAPSO "
                    + " FROM" //lay so luong cuoi ky thang truoc la dau ky thang nay
                    + " (SELECT " + dkthamgia + " as dauky, " + dkcapso + " as daukycapso,"
                    + " ISNULL(sum(case when b.Pa in ('ON','TM','TC','TD') then 1 else 0 end),0) as tangthamgia,"
                    + " ''  as tangcapso,"
                    + " ISNULL(sum(case when b.Pa in ('GH','TS','OF','GD','GC') then 1 else 0 end),0) as giamthamgia,"
                    + " '' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY"
                    + " FROM HSC47_Hdr a, HSC47_Dtl b "
                    + " WHERE a.KeySL= b.Keysl AND a.Madvi='" + strMaDvi + "'  "
                    + " AND a.Ma_Nte='VND'  AND LEFT(a.Maqt,6)= " + strNam + i.ToString("D2") + " AND  DENTHANG =  " + strNam + i.ToString("D2")
                    + " union all "
                    + " SELECT '' as dauky, '' as daukycapso, '' as tangthamgia,  "
                    + " ISNULL(sum(case when b.Pa in ('ON','TM','TC','TD') then 1 else 0 end),0) as tangcapso, '' as giamthamgia,"
                    + " ISNULL(sum(case when b.Pa in ('GH','TS','OF','GD','GC') then 1 else 0 end),0) as giamcapso,"
                    + " '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY  "
                    + " FROM HSC47_Hdr a, HSC47_Dtl b  WHERE a.KeySL= b.Keysl AND a.Madvi='" + strMaDvi + "'"
                    + " AND a.Ma_Nte='VND'  AND LEFT(a.Maqt,6)= " + strNam + i.ToString("D2") + " AND  DENTHANG =  " + strNam + i.ToString("D2")
                    + " AND ((left(b.Sobhxh,2) <> '" + Settings.Default.MaTam + "' and left(b.sobhxh,6) not in (" + strSobhxh + "))  "
                    + " or left(b.sobhxh,6) in (" + strSobhxh + ") and b.Sobhxh in ( "
                    + " select sobhxh from " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL where ngaycap < a.ngayct and madvi = a.madvi)) "
                    + " union all"
                    + " SELECT '' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,"
                    + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN in ( 'QT','' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as QT,"
                    + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'BL' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as BL,"
                    + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'HT' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as HT,"
                    + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'TU' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as TT,"
                    + " ISNULL(SUM(CASE WHEN  Intr_hdr.LOAI =  '2' then 1 else 0 end),0) as TRCL,"
                    + " '' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY"
                    + " FROM  " + Settings.Default.DBName_Sothe + ".dbo.INTR_HDR Intr_hdr WHERE  Intr_hdr.MADVI =  '" + strMaDvi + "'   AND  "
                    + " LEFT(CONVERT(nvarchar(20), Intr_hdr.NGAYCAP, 111),7) = '" + strNam + "/" + i.ToString("D2") + "'"
                    + " UNION ALL"
                    + " SELECT '' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,"
                    + " sum(case when Inso_dtl.LOAI = '1' then 1 else 0 end) AS TONGBIA,'' as TongBiaTang,"
                    + " sum(case when Inso_dtl.LOAI = '2' then 1 else 0 end) AS BIACAPLAI,"
                    + " '' as BIATRONGKY"
                    + " FROM " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL Inso_dtl "
                    + " WHERE LEFT(CONVERT(nvarchar(20), Inso_dtl.NGAYCAP, 111),7) = '" + strNam + "/" + i.ToString("D2") + "'"
                    + " AND Inso_dtl.MADVI = '" + strMaDvi + "'   "
                    + " union all "
                    + " select '' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,"
                    + " '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL, "
                    + " '' AS TONGBIA,count(sobhxh) as TongBiaTang ,'' AS BIACAPLAI, '' as BIATRONGKY FROM "
                    + " MISBHXH_SOTHE_NT.dbo.INSO_DTL Inso_dtl  LEFT JOIN DMDONVI d on d.Madvi = Inso_dtl.Madvi  "
                    + " WHERE LEFT(CONVERT(nvarchar(20), Inso_dtl.NGAYCAP, 111),7) = '" + strNam + "/" + i.ToString("D2") + "' and Inso_dtl.madvi = '" + strMaDvi + "'   and Inso_dtl.LOAI = '1' "
                    + " and sobhxh in ("
                    + " select d.sobhxh FROM HSC47_Hdr c, HSC47_Dtl d where"
                    + " c.KeySL= d.Keysl AND c.Ma_Nte='VND' and c.madvi = Inso_dtl.madvi and"
                    + " d.pa not in ('GH','TS','OF','GD','GC','TT') and sobhxh = d.sobhxh and"
                    + " c.Maqt in("
                    + " select max(a.Maqt) FROM HSC47_Hdr a, HSC47_Dtl b "
                    + " WHERE a.KeySL= b.Keysl and b.sobhxh = d.sobhxh  and a.ngayct < ngaycap AND b.DENTHANG = LEFT(a.Maqt,6)"
                    + " ))"
                    + " union all "
                    + " select  '' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' as TONGBIA,'' as TongBiaTang,'' as BIACAPLAI,"
                    + " count(b.sobhxh) as BIATRONGKY FROM HSC47_Hdr a, HSC47_Dtl b, " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL c "
                    + " WHERE a.KeySL= b.Keysl and b.sobhxh = c.sobhxh AND a.Madvi='" + strMaDvi + "'  "
                    + " AND a.Ma_Nte='VND' and b.Pa in ('ON','TM','TC','TD') AND LEFT(a.Maqt,6)= " + strNam + i.ToString("D2") + " and LEFT(CONVERT(nvarchar(20), c.NGAYCAP, 111),7) = '" + strNam + "/" + i.ToString("D2") + "') AS TEMP ";

                    if (dsBCCT.Tables[0].Rows.Count == 0)
                    {
                        break;
                    }

                    using (SqlConnection connection =
                           new SqlConnection(connectionString))
                    {
                        SqlCommand command = connection.CreateCommand();
                        command.CommandText = queryString;
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            DataRow newRow = dsBCCT.Tables[0].NewRow();
                            newRow["Madvi"] = strMaDvi;
                            newRow["TenDvi"] = dsBCCT.Tables[0].Rows[dsBCCT.Tables[0].Rows.Count - 1]["TenDvi"];
                            newRow["thang"] = "tháng " + i;
                            newRow["dauky"] = dkthamgia;
                            newRow["daukycapso"] = dkcapso;
                            newRow["tangthamgia"] = reader["tangthamgia"];
                            newRow["tangcapso"] = reader["tangcapso"];
                            newRow["giamthamgia"] = reader["giamthamgia"];
                            newRow["QT"] = reader["QT"];
                            newRow["BL"] = reader["BL"];
                            newRow["HT"] = reader["HT"];
                            newRow["TT"] = reader["TT"];
                            newRow["TONGTRGIAM"] = reader["TONGTRGIAM"];
                            newRow["TONGTR"] = reader["TONGTR"];
                            newRow["TRCL"] = reader["TRCL"];
                            newRow["TONGBIA"] = reader["TONGBIA"];
                            newRow["BIATRONGKY"] = reader["BIATRONGKY"];
                            newRow["BIAKYTRUOC"] = reader["BIAKYTRUOC"];
                            newRow["BIACAPLAI"] = reader["BIACAPLAI"];
                            newRow["ckthamgia"] = reader["ckthamgia"];
                            newRow["ckcapso"] = reader["ckcapso"];
                            dkthamgia = (int)reader["ckthamgia"];
                            dkcapso = (int)reader["ckcapso"];
                            dsBCCT.Tables[0].Rows.Add(newRow);
                        }
                        reader.Close();
                    }
                    if (i == 3 || i == 6 || i == 9 || i == 12) // tinh so luong tung quy
                    {
                        //lay tong so luong cua tung quy
                        DataRow newRow = dsBCCT.Tables[0].NewRow();
                        x = dsBCCT.Tables[0].Rows.Count;
                        newRow["Madvi"] = strMaDvi;
                        newRow["TenDvi"] = dsBCCT.Tables[0].Rows[dsBCCT.Tables[0].Rows.Count - 1]["TenDvi"];
                        newRow["thang"] = "Cộng Quý";

                        newRow["dauky"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["dauky"]);
                        newRow["daukycapso"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["daukycapso"]);
                                              
                        newRow["tangthamgia"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["tangthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["tangthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["tangthamgia"]);
                        newRow["tangcapso"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["tangcapso"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["tangcapso"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["tangcapso"]);
                        newRow["giamthamgia"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["giamthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["giamthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["giamthamgia"]);
                        newRow["QT"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["QT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["QT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["QT"]);
                        newRow["BL"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["BL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["BL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["BL"]);
                        newRow["HT"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["HT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["HT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["HT"]);
                        newRow["TT"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["TT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["TT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["TT"]);
                        newRow["TONGTRGIAM"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["TONGTRGIAM"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["TONGTRGIAM"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["TONGTRGIAM"]);
                        newRow["TONGTR"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["TONGTR"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["TONGTR"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["TONGTR"]);
                        newRow["TRCL"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["TRCL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["TRCL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["TRCL"]);
                        newRow["TONGBIA"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["TONGBIA"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["TONGBIA"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["TONGBIA"]);
                        newRow["BIATRONGKY"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["BIATRONGKY"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["BIATRONGKY"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["BIATRONGKY"]);
                        newRow["BIAKYTRUOC"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["BIAKYTRUOC"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["BIAKYTRUOC"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["BIAKYTRUOC"]);
                        newRow["BIACAPLAI"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 3]["BIACAPLAI"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 2]["BIACAPLAI"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["BIACAPLAI"]);

                        newRow["ckthamgia"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["ckthamgia"]);
                        newRow["ckcapso"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 1]["ckcapso"]);

                        dsBCCT.Tables[0].Rows.Add(newRow);

                    }
                    if (i == 12)//thang 12 lay tong so luong 3 quy
                    {
                        DataRow newRow = dsBCCT.Tables[0].NewRow();
                        x = dsBCCT.Tables[0].Rows.Count - 1;
                        newRow["Madvi"] = strMaDvi;
                        newRow["TenDvi"] = dsBCCT.Tables[0].Rows[dsBCCT.Tables[0].Rows.Count - 1]["TenDvi"];
                        newRow["thang"] = "Lũy Kế";

                        newRow["dauky"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["dauky"]);
                        newRow["daukycapso"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["daukycapso"]);

                        newRow["tangthamgia"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["tangthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["tangthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["tangthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["tangthamgia"]);
                        newRow["tangcapso"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["tangcapso"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["tangcapso"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["tangcapso"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["tangcapso"]);
                        newRow["giamthamgia"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["giamthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["giamthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["giamthamgia"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["giamthamgia"]);
                        newRow["QT"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["QT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["QT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["QT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["QT"]);
                        newRow["BL"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["BL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["BL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["BL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["BL"]);
                        newRow["HT"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["HT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["HT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["HT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["HT"]);
                        newRow["TT"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["TT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["TT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["TT"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["TT"]);
                        newRow["TONGTRGIAM"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["TONGTRGIAM"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["TONGTRGIAM"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["TONGTRGIAM"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["TONGTRGIAM"]);
                        newRow["TONGTR"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["TONGTR"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["TONGTR"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["TONGTR"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["TONGTR"]);
                        newRow["TRCL"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["TRCL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["TRCL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["TRCL"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["TRCL"]);
                        newRow["TONGBIA"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["TONGBIA"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["TONGBIA"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["TONGBIA"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["TONGBIA"]);
                        newRow["BIATRONGKY"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["BIATRONGKY"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["BIATRONGKY"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["BIATRONGKY"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["BIATRONGKY"]);
                        newRow["BIAKYTRUOC"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["BIAKYTRUOC"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["BIAKYTRUOC"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["BIAKYTRUOC"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["BIAKYTRUOC"]);
                        newRow["BIACAPLAI"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["BIACAPLAI"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 4]["BIACAPLAI"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 8]["BIACAPLAI"]) + System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x - 12]["BIACAPLAI"]);
                                             
                        newRow["ckthamgia"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["ckthamgia"]);
                        newRow["ckcapso"] = System.Convert.ToInt16(dsBCCT.Tables[0].Rows[x]["ckcapso"]);

                        dsBCCT.Tables[0].Rows.Add(newRow);
                    }

                }


            }
            Cursor.Current = Cursors.Default;
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpBCCT.rpt");
            cr.SetDataSource(dsBCCT);
            f.crpView.ReportSource = cr;
            f.Show();

        }
        //
        //in mau 10
        private void btn10sbh_Click(object sender, EventArgs e)
        {
            if (Check10SBH() == false) { return; }
            int dkthamgia = 0, dkcapso = 0;//so luong dau ky tham gia, dau ky cap so
            string strNamTruoc = System.Convert.ToInt16(txtNam.Text) - 1 + "12";//lay thang 12 nam truoc
            string strNamthang = txtNam.Text + "01";//thang dau nam
            string strNamthang1 = txtNam.Text + "/01";//thang dau nam dang format khac
            string strNam = txtNam.Text;
            string strSobhxh = Properties.Settings.Default.MaTinh + txtNam.Text.ToString().Substring(2); //lay tien to sobhxh
            string queryString = null;
            DataRow newRow;
            int intThang = 0;
            bcctbhxh dsBCCT = new bcctbhxh();
            intThang = (cboQuy.SelectedIndex) * 3 + 1;//thang cuoi cung cua quy
            //danh dach cac thang trong quy
            strNamthang1 = "('" + txtNam.Text + "/" + String.Format("{0:00}", intThang) + "','" + txtNam.Text + 
                "/" + String.Format("{0:00}", intThang + 1) + "','" + txtNam.Text + "/" + String.Format("{0:00}", intThang + 2) +
                "')";
            //thang dau ky trong quy
            strNamthang = txtNam.Text + String.Format("{0:00}", intThang);
            strSobhxh = ArrayBhxh();//danh sach cac tien to so bhxh duoc cap trong nam
            string strTuthang = Convert.ToInt16(dtThang.Value.Month - 1) + "/26/" + dtThang.Value.Year; //tinh tu 26 thang truoc
            //string strDenthang = dtThang.Value.Month + "/" + DateTime.DaysInMonth(dtThang.Value.Year,dtThang.Value.Month) + "/" + dtThang.Value.Year;// tinh den 25 thang nay
            string strDenthang = dtThang.Value.Month + "/" + 25 + "/" + dtThang.Value.Year;// tinh den 25 thang nay
            
            Cursor.Current = Cursors.WaitCursor;
            if (cboQuy.SelectedIndex == 0) // truong hop quy I tinh so luong dau ki nam
            {
                strTuthang = "12/26/" + (dtThang.Value.Year - 1); //tinh tu 26 thang truoc
                queryString = " SELECT a.Makhoi,b.Tenkhoi,SUM(dauky) as dauky,sum(daukycapso) as daukycapso, sum(tangthamgia) as tangthamgia ,"
                       + " sum(tangcapso) as tangcapso,sum(giamthamgia) as giamthamgia, SUM(QT) AS QT, SUM(BL) as BL,SUM(HT) as HT,SUM(TT) as TT,"
                       + "  SUM(BL) + SUM(HT) + SUM(TT) AS TONGTRGIAM, SUM(BL) + SUM(HT) + SUM(TT) + SUM(QT) AS TONGTR, "
                       + "  SUM(TRCL) as TRCL, SUM(TONGBIA) AS TONGBIA, sum(BIATRONGKY) as BIATRONGKY,SUM(TONGBIA) - "
                       + "  sum(BIATRONGKY) as BIAKYTRUOC,SUM(BIACAPLAI) AS BIACAPLAI, SUM(dauky) + sum(tangthamgia) -"
                       + "  sum(giamthamgia) AS CKTHAMGIA,  sum(daukycapso) + sum(tangcapso) - sum(giamcapso) + "
                       + "  SUM(TongBiaTang) AS CKCAPSO  FROM ("
                       + "  SELECT d.makhoi, count(a.Sobhxh) as dauky,  "
                       + "  sum(case when right(left(b.Sobhxh,2),1) = 'A' or "
                       + "  left(a.sobhxh,6) in  (" + strSobhxh + ") "
                       + "  then  0 else 1 end)  as daukycapso, '' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso, '' as QT, "
                       + "  '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY "
                       + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh left join dmdonvi d on d.madvi = b.madvi,   (SELECT Sobhxh, MAX(a.Maqt) AS Maqt FROM HSC47_DTL  a, HSC47_Hdr b "
                       + "  WHERE a.KeySL= b.Keysl and a.Loaidt in ('ML','HS') AND LEFT(a.Maqt,6)=Denthang  AND "
                       + "  b.NGAYCT < '" + strTuthang + "' AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY Sobhxh) a   "
                       + "  WHERE a.Sobhxh=b.Sobhxh AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
                       + "  AND b.Loaidt IN ('ML','HS')  AND left(b.Madvi,1) <> 'B'  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Ml+b.Mlyt<>0 AND  "
                       + "  CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 group by d.makhoi ";
            }
            else //lay so luong dau ky tu table BC10SHB
            {
                queryString = " SELECT a.Makhoi,b.Tenkhoi,SUM(dauky) as dauky,sum(daukycapso) as daukycapso, sum(tangthamgia) as tangthamgia ,"
                  + " sum(tangcapso) as tangcapso,sum(giamthamgia) as giamthamgia, SUM(QT) AS QT, SUM(BL) as BL,SUM(HT) as HT,SUM(TT) as TT,"
                  + "  SUM(BL) + SUM(HT) + SUM(TT) AS TONGTRGIAM, SUM(BL) + SUM(HT) + SUM(TT) + SUM(QT) AS TONGTR, "
                  + "  SUM(TRCL) as TRCL, SUM(TONGBIA) AS TONGBIA, sum(BIATRONGKY) as BIATRONGKY,SUM(TONGBIA) - "
                  + "  sum(BIATRONGKY) as BIAKYTRUOC,SUM(BIACAPLAI) AS BIACAPLAI, SUM(dauky) + sum(tangthamgia) -"
                  + "  sum(giamthamgia) AS CKTHAMGIA,  sum(daukycapso) + sum(tangcapso) - sum(giamcapso) + "
                  + "  SUM(TongBiaTang) AS CKCAPSO  FROM ("
                  + "  SELECT makhoi, CKTHAMGIA as dauky,  "
                  + "  CKCAPSO  as daukycapso, '' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso, '' as QT, "
                  + "  '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY "
                  + "  FROM BC10SHB WHERE nam = '" + txtNam.Text + "' and quy = " + Convert.ToInt16(cboQuy.SelectedIndex);
            }
            queryString = queryString + "  union all " // tang tham gia, giam tham ga
                   + "  SELECT d.Makhoi,'' as dauky, '' as daukycapso, ISNULL(SUM(CASE WHEN CHARINDEX(B.Pa,'TT/TU/TN/GN/DN/CD')=0 AND (ML_DK+HSL_DK)=0 AND (B.ML+B.HSL)<>0 THEN 1 ELSE 0 END), 0) as "
                   + "  tangthamgia, ''  as tangcapso, ISNULL(SUM(CASE WHEN CHARINDEX(B.Pa,'TT/TU/TN/GN/DN/CD')=0 AND (ML_DK+HSL_DK)<>0 AND (B.ML+B.HSL)=0 THEN 1 ELSE 0 END), 0) as giamthamgia,"
                   + "  '' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,"
                   + "  '' as BIATRONGKY FROM HSC47_Hdr a, HSC47_Dtl b,dmdonvi d  WHERE "
                   + "  a.KeySL= b.Keysl and a.madvi = d.madvi AND left(a.Madvi,1) <> 'B' AND "
                   + "  LEFT(a.Maqt,6) = DENTHANG AND A.NGAYCT >= '12/26/2011' AND A.NGAYCT <= '3/25/2012' "
                   + "  group by d.Makhoi  "
                   + "  union all  "// thang cap so, giam cap so
                   + "  SELECT d.Makhoi,'' as dauky, '' as daukycapso,"
                   + "  '' as tangthamgia,   ISNULL(SUM(CASE WHEN CHARINDEX(B.Pa,'TT/TU/TN/GN/DN/CD')=0 AND (ML_DK+HSL_DK)=0 AND (B.ML+B.HSL)<>0 THEN 1 ELSE 0 END), 0) as tangcapso, "
                   + "  '' as giamthamgia,   ISNULL(SUM(CASE WHEN CHARINDEX(B.Pa,'TT/TU/TN/GN/DN/CD')=0 AND (ML_DK+HSL_DK)<>0 AND (B.ML+B.HSL)=0 THEN 1 ELSE 0 END), 0) as giamcapso,  "
                   + "  '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,'' as BIATRONGKY"
                   + "  FROM HSC47_Hdr a, HSC47_Dtl b,dmdonvi d  WHERE a.KeySL= b.Keysl and a.madvi = d.madvi AND left(a.Madvi,1) <> 'B' AND "
                   + "  LEFT(a.Maqt,6) = DENTHANG AND A.NGAYCT >= '12/26/2011' AND A.NGAYCT <= '3/25/2012' "
                   + "  AND ((right(left(b.Sobhxh,2),1) <> 'A' and left(b.sobhxh,6) not in (" + strSobhxh + ")) "
                   + "  or left(b.sobhxh,6) in (" + strSobhxh + ") and b.Sobhxh in (select sobhxh from " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL "
                   + "  where ngaycap < a.ngayct and madvi = a.madvi and sobhxh = b.sobhxh))  group by d.Makhoi "
                   + "  union all  " //so luong to roi theo qt,bl,ht,tt va to roi cap lai
                   + "  SELECT d.Makhoi,'' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN in ( 'QT','' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as QT, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN in ( 'BL','CS' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as BL, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'HT' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as HT, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'TU' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as TT, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAI =  '2' then 1 else 0 end),0) as TRCL, '' AS TONGBIA,'' as TongBiaTang,"
                   + "  '' AS BIACAPLAI,'' as BIATRONGKY FROM  " + Settings.Default.DBName_Sothe + ".dbo.INTR_HDR Intr_hdr, dmdonvi d"
                   + "  WHERE  "
                   + "  d.Madvi = Intr_hdr.Madvi AND left(Intr_hdr.Madvi,1) <> 'B' and Dain <> 0"
                   + "  and Intr_hdr.NGAYCAP >= '12/26/2011' AND Intr_hdr.NGAYCAP <= '3/25/2012' "
                   + "  group by d.Makhoi  "
                   + "  UNION ALL " // tong bia, bia cap lai
                   + "  SELECT d.makhoi,'' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,"
                   + "  '' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL, "
                   + "  sum(case when Inso_dtl.LOAI = '1' then 1 else 0 end) AS TONGBIA,'' as TongBiaTang, sum(case when Inso_dtl.LOAI = '2' "
                   + "  then 1 else 0 end) AS BIACAPLAI, '' as BIATRONGKY FROM MISBHXH_SOTHE_NT.dbo.INSO_DTL Inso_dtl, dmdonvi d "
                   + "  WHERE "
                   + "  d.Madvi = Inso_dtl.MADVI and left(Inso_dtl.Madvi,1) <> 'B'"
                   + "  and Inso_dtl.NGAYCAP >= '12/26/2011' AND Inso_dtl.NGAYCAP <= '3/25/2012' " 
                   + "  group by d.makhoi  "
                   + "  union all " //tong bia tang 
                   + "  select d.makhoi,'' as dauky, '' as daukycapso,'' as tangthamgia ,"
                   + "  '' as tangcapso,'' as giamthamgia,'' as giamcapso, '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,  "
                   + "  '' AS TONGBIA,count(sobhxh) as TongBiaTang ,'' AS BIACAPLAI, '' as BIATRONGKY "
                   + "  FROM  " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL Inso_dtl,DMDONVI d     "
                   + "  WHERE d.Madvi = Inso_dtl.Madvi and left(Inso_dtl.Madvi,1) <> 'B' and "
                   + "  Inso_dtl.NGAYCAP >= '12/26/2011' AND Inso_dtl.NGAYCAP <= '3/25/2012' " 
                   + "  and Inso_dtl.LOAI = '1'  and sobhxh in ( select d.sobhxh FROM HSC47_Hdr c, HSC47_Dtl d "
                   + "  where c.KeySL= d.Keysl and c.madvi = Inso_dtl.madvi and d.pa not in ('GH','TS','OF','GD','GC','TT')"
                   + "  and sobhxh = d.sobhxh and c.Maqt in( select max(a.Maqt) FROM HSC47_Hdr a, HSC47_Dtl b  "
                   + "  WHERE a.KeySL= b.Keysl and b.sobhxh = d.sobhxh  and a.ngayct < ngaycap AND "
                   + "  b.DENTHANG = LEFT(a.Maqt,6))) "
                   + "  group by d.makhoi"
                   + "  union all  "//tong bia tang trong ky
                   + "  select d.makhoi, '' as dauky, '' as daukycapso,'' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso,"
                   + "  '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' as TONGBIA,'' as TongBiaTang,'' as BIACAPLAI, "
                   + "  count(b.sobhxh) as BIATRONGKY FROM HSC47_Hdr a, HSC47_Dtl b, " + Settings.Default.DBName_Sothe + ".dbo.INSO_DTL c,DMDONVI d  "
                   + "  WHERE d.Madvi = a.Madvi and left(a.Madvi,1) <> 'B' AND  C.LOAI = '1' and a.KeySL= b.Keysl and b.sobhxh = c.sobhxh and b.Pa in "
                   + "  ('ON','TM','TC','TD') AND  LEFT(a.Maqt,6) = DENTHANG AND "
                   + " c.NGAYCAP >= '12/26/2011' AND c.NGAYCAP <= '3/25/2012'"
                   + "  group by d.makhoi "
                   + "  ) a , Dmkhoi b where a.makhoi = b.makhoi  "
                   + "  group by a.makhoi,b.Tenkhoi "
                   + "  order by a.Makhoi ";



            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    newRow = dsBCCT.Tables[0].NewRow();
                    newRow["Madvi"] = cboQuy.Text;
                    newRow["TenDvi"] = reader["Tenkhoi"];
                    newRow["thang"] = reader["Makhoi"];
                    newRow["dauky"] = reader["dauky"];
                    newRow["daukycapso"] = reader["daukycapso"];
                    newRow["tangthamgia"] = reader["tangthamgia"];
                    newRow["tangcapso"] = reader["tangcapso"];
                    newRow["giamthamgia"] = reader["giamthamgia"];
                    newRow["QT"] = reader["QT"];
                    newRow["BL"] = reader["BL"];
                    newRow["HT"] = reader["HT"];
                    newRow["TT"] = reader["TT"];
                    newRow["TONGTRGIAM"] = reader["TONGTRGIAM"];
                    newRow["TONGTR"] = reader["TONGTR"];
                    newRow["TRCL"] = reader["TRCL"];
                    newRow["TONGBIA"] = reader["TONGBIA"];
                    newRow["BIATRONGKY"] = reader["BIATRONGKY"];
                    newRow["BIAKYTRUOC"] = reader["BIAKYTRUOC"];
                    newRow["BIACAPLAI"] = reader["BIACAPLAI"];
                    newRow["ckthamgia"] = reader["ckthamgia"];
                    newRow["ckcapso"] = reader["ckcapso"];
                    dkthamgia = (int)reader["ckthamgia"];
                    dkcapso = (int)reader["ckcapso"];
                    dsBCCT.Tables[0].Rows.Add(newRow);
                }
                reader.Close();
            }

            //lay tong so luong  quy
            newRow = dsBCCT.Tables[0].NewRow();
            newRow["dauky"] = 0; newRow["daukycapso"] = 0; newRow["tangthamgia"] = 0;
            newRow["tangcapso"] = 0; newRow["giamthamgia"] = 0; newRow["QT"] = 0;
            newRow["BL"] = 0; newRow["HT"] = 0; newRow["TT"] = 0; newRow["TONGTRGIAM"] = 0;
            newRow["TONGTR"] = 0; newRow["TRCL"] = 0; newRow["TONGBIA"] = 0;
            newRow["BIATRONGKY"] = 0; newRow["BIAKYTRUOC"] = 0; newRow["BIACAPLAI"] = 0;
            newRow["ckthamgia"] = 0; newRow["ckcapso"] = 0;
            using (SqlConnection connection =
                    new SqlConnection(connectionString))
            {

                SqlCommand command = connection.CreateCommand();
                connection.Open();
                command.CommandText = queryString;
                queryString = "delete BC10SHB where nam = '" + txtNam.Text + "' and quy = " + Convert.ToInt16(cboQuy.SelectedIndex + 1);
                command.CommandText = queryString;
                command.ExecuteNonQuery();
                for (int i = 0; i < dsBCCT.Tables[0].Rows.Count; i++)
                {
                    newRow["Madvi"] = cboQuy.Text;
                    newRow["thang"] = "Cộng Quý";
                    newRow["dauky"] = Convert.ToInt32(newRow["dauky"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dauky"]);
                    newRow["daukycapso"] = Convert.ToInt32(newRow["daukycapso"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["daukycapso"]);
                    newRow["tangthamgia"] = Convert.ToInt32(newRow["tangthamgia"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["tangthamgia"]);
                    newRow["tangcapso"] = Convert.ToInt32(newRow["tangcapso"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["tangcapso"]);
                    newRow["giamthamgia"] = Convert.ToInt32(newRow["giamthamgia"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["giamthamgia"]);
                    newRow["QT"] = Convert.ToInt32(newRow["QT"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["QT"]);
                    newRow["BL"] = Convert.ToInt32(newRow["BL"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BL"]);
                    newRow["HT"] = Convert.ToInt32(newRow["HT"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["HT"]);
                    newRow["TT"] = Convert.ToInt32(newRow["TT"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TT"]);
                    newRow["TONGTRGIAM"] = Convert.ToInt32(newRow["TONGTRGIAM"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TONGTRGIAM"]);
                    newRow["TONGTR"] = Convert.ToInt32(newRow["TONGTR"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TONGTR"]);
                    newRow["TRCL"] = Convert.ToInt32(newRow["TRCL"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TRCL"]);
                    newRow["TONGBIA"] = Convert.ToInt32(newRow["TONGBIA"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TONGBIA"]);
                    newRow["BIATRONGKY"] = Convert.ToInt32(newRow["BIATRONGKY"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BIATRONGKY"]);
                    newRow["BIAKYTRUOC"] = Convert.ToInt32(newRow["BIAKYTRUOC"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BIAKYTRUOC"]);
                    newRow["BIACAPLAI"] = Convert.ToInt32(newRow["BIACAPLAI"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BIACAPLAI"]);
                    newRow["ckthamgia"] = Convert.ToInt32(newRow["ckthamgia"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["ckthamgia"]);
                    newRow["ckcapso"] = Convert.ToInt32(newRow["ckcapso"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["ckcapso"]);

                    //dua tong so luong quy vao table BC10SHB
                    queryString = "exec sp_executesql N'INSERT INTO BC10SHB (MAKHOI,QUY,NAM,CKTHAMGIA,CKCAPSO)"
                         + " VALUES (@P1,@P2,@P3,@P4,@P5)', N'@P1 char(2),@P2 tinyint,@P3 char(4),@P4 int,@P5 int', "
                         + " '" + dsBCCT.Tables[0].Rows[i]["thang"] + "', " + Convert.ToInt16(cboQuy.SelectedIndex + 1) + ", '" + txtNam.Text + "', "
                         + dsBCCT.Tables[0].Rows[i]["ckthamgia"] + ", '" + dsBCCT.Tables[0].Rows[i]["ckcapso"] + "'";
                    command.CommandText = queryString;
                    command.ExecuteNonQuery();
                }
            }
            dsBCCT.Tables[0].Rows.Add(newRow); //add so tong so cua quy


            Cursor.Current = Cursors.Default;
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpBC10.rpt");
            cr.SetDataSource(dsBCCT);
            f.crpView.ReportSource = cr;
            f.Show();
        }
        #endregion

        private void btnBC1_Click(object sender, EventArgs e)
        {

            
            //in tong hop 
            string strHearder;
            string strFootter;
            string strTable;

            string strNamTruoc = System.Convert.ToInt16(txtNam.Text) - 1 + "12";//lay thang 12 nam truoc
            string strNamthangtruoc;
            string strNamthang = txtNam.Text + "01";//thang dau nam
            string strNamthang1 = txtNam.Text + "/01";//thang dau nam dang format khac
            string strNam = txtNam.Text;
            string strSobhxh = Properties.Settings.Default.MaTinh + txtNam.Text.ToString().Substring(2); //lay tien to sobhxh
            string queryString = null;
            int intNam = dtThang.Value.Year;
            int intThang = Convert.ToInt16(dtThang.Value.Month - 1);
            string strTuthang = Convert.ToInt16(dtThang.Value.Month-1) + "/26/" + dtThang.Value.Year; //tinh tu 26 thang truoc
            //string strDenthang = dtThang.Value.Month + "/" + DateTime.DaysInMonth(dtThang.Value.Year,dtThang.Value.Month) + "/" + dtThang.Value.Year;// tinh den 25 thang nay
            string strDenthang = dtThang.Value.Month + "/" + 25 + "/" + dtThang.Value.Year;// tinh den 25 thang nay

            strNamthangtruoc = dtThang.Value.Year.ToString() + String.Format("{0:00}", dtThang.Value.Month-1);
            
            System.Windows.Forms.ButtonBase btnTam = (System.Windows.Forms.ButtonBase)sender;
            if (btnTam.Name == "btnBCT")
            {
                strHearder = "SELECT makhoi, '' as tenkhoi, SUM(dauky) as dauky,sum(daukycapso) as daukycapso,"
                   + " sum(DauKyToRoiHangNam) as DauKyToRoiHangNam,sum(DauKyToRoiChotSo) as DauKyToRoiChotSo,"
                   + "  sum(DauKyBiaCapLai) as DauKyBiaCapLai,sum(DAUKYNGUOICHOTSOXN) as DAUKYNGUOICHOTSOXN,  "
                   + "  sum(tangthamgia) as tangthamgia ,sum(tangcapso) as tangcapso,"
                   + "  sum(giamthamgia) as giamthamgia, SUM(QT) AS QT, SUM(BL) as BL,SUM(HT) as HT, 0 as TT,"
                   + "  SUM(TONGTRGIAM) AS TONGTRGIAM, "
                   + "  SUM(TONGTR) AS TONGTR,   SUM(TRCL) as TRCL, "
                   + "  SUM(TONGBIA) AS TONGBIA, sum(BIATRONGKY) as BIATRONGKY,SUM(BIAKYTRUOC) as BIAKYTRUOC,"
                   + "  SUM(BIACAPLAI) AS BIACAPLAI, SUM(CKTHAMGIA) AS CKTHAMGIA,sum(NGUOICHOTSOXN) as NGUOICHOTSOXN "
                   + "  From ( "
                   + "  SELECT  a.Makhoi as MK, "
                   + "        CASE WHEN a.Makhoi = 'IS' THEN 'IS'"
                   + "        WHEN a.Makhoi = 'FA'  THEN 'FA'"
                   + "        ELSE 'TA'"
                   + "        END AS makhoi,";

                strFootter = " ) TT group by TT.makhoi";
                strTable = "BC01T";
            }
            else
            {
                strHearder = "SELECT a.Makhoi,";
                strFootter  = " order by a.Makhoi ";
                strTable = "BC01";
            }

            if (dtThang.Value.Month == 1)
            {
                strTuthang = "12/26/" + (dtThang.Value.Year-1); //tinh tu 26 thang truoc
                strNamthangtruoc = (dtThang.Value.Year-1).ToString() + String.Format("{0:00}", 12);
                
            }

            DataRow newRow;
            bcctbhxh dsBCCT = new bcctbhxh();
             //danh dach cac thang trong quy
            strNamthang1 = "('" + dtThang.Value.Year.ToString() + "/" + String.Format("{0:00}", dtThang.Value.Month) + "')";
            //thang dau ky trong quy
            strNamthang = dtThang.Value.Year.ToString() + String.Format("{0:00}", dtThang.Value.Month);
            

            strSobhxh = ArrayBhxh();//danh sach cac tien to so bhxh duoc cap trong nam
            if (dtThang.Value.Month == 1) // truong hop dau nam, giam nam, thang=12
            {
                intNam = intNam - 1;
                intThang = 12;
            }
            Cursor.Current = Cursors.WaitCursor;
            if (dtThang.Value.Month == 1 && dtThang.Value.Year == 2012) // truong hop thang 1 tinh so luong dau ki nam
            {
                queryString = strHearder + "b.Tenkhoi,SUM(dauky) as dauky,sum(daukycapso) as daukycapso, "
                       + "  sum(DauKyToRoiHangNam) as DauKyToRoiHangNam,sum(DauKyToRoiChotSo) as DauKyToRoiChotSo,sum(DauKyBiaCapLai) as DauKyBiaCapLai,sum(DAUKYNGUOICHOTSOXN) as DAUKYNGUOICHOTSOXN,"
                       + "  sum(tangthamgia) as tangthamgia ,sum(tangcapso) as tangcapso,sum(giamthamgia) as giamthamgia, SUM(QT) AS QT, SUM(BL) as BL,SUM(HT) as HT,SUM(TT) as TT,"
                       + "  SUM(BL) + SUM(HT) + SUM(TT) AS TONGTRGIAM, SUM(BL) + SUM(HT) + SUM(TT) + SUM(QT) AS TONGTR, "
                       + "  SUM(TRCL) as TRCL, SUM(TONGBIA) AS TONGBIA, sum(BIATRONGKY) as BIATRONGKY,SUM(TONGBIA) - "
                       + "  sum(BIATRONGKY) as BIAKYTRUOC,SUM(BIACAPLAI) AS BIACAPLAI, SUM(dauky) + sum(tangthamgia) -"
                       + "  sum(giamthamgia) AS CKTHAMGIA,sum(NGUOICHOTSOXN) as NGUOICHOTSOXN FROM ("
                       + "  SELECT d.makhoi, count(a.Sobhxh) as dauky, 0  as daukycapso, " //dau ky tang tham gia
                       + "  0 as DauKyToRoiHangNam, 0 as DauKyToRoiChotSo, 0 as DauKyBiaCapLai,0 as DAUKYNGUOICHOTSOXN, "
                       + "  0 as tangthamgia ,0 as tangcapso,0 as giamthamgia,0 as giamcapso, 0 as QT, "
                       + "  0 as BL,0  as HT,0  as TT,0  as TRCL,0 AS TONGBIA,0 as TongBiaTang,0 AS BIACAPLAI,0 as BIATRONGKY,0 as NGUOICHOTSOXN  "
                       + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh left join dmdonvi d on d.madvi = b.madvi,"
                       + " (SELECT Sobhxh, MAX(f.Maqt) AS Maqt FROM HSC47_DTL f inner join  HSC47_Hdr e on e.keysl = f.keysl  WHERE f.Loaidt in ('ML','HS') AND "
                       + "  LEFT(f.Maqt,6)=f.Denthang and e.NGAYCT <= '12/25/2011' AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY Sobhxh) a   "
                       + "  WHERE a.Sobhxh=b.Sobhxh AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
                       + "  AND b.Loaidt IN ('ML','HS')  AND left(b.Madvi,1) <> 'B'  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Ml+b.Mlyt<>0 AND  "
                       + "  CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 group by d.makhoi ";

            }
            else //lay so luong dau ky tu table BC10SHB
            {

                queryString = strHearder + " b.Tenkhoi,SUM(dauky) as dauky,sum(daukycapso) as daukycapso, "
                 + " sum(DauKyToRoiHangNam) as DauKyToRoiHangNam,sum(DauKyToRoiChotSo) as DauKyToRoiChotSo,sum(DauKyBiaCapLai) as DauKyBiaCapLai,sum(DAUKYNGUOICHOTSOXN) as DAUKYNGUOICHOTSOXN,"
                 + " sum(tangthamgia) as tangthamgia ,sum(tangcapso) as tangcapso,sum(giamthamgia) as giamthamgia, SUM(QT) AS QT, SUM(BL) as BL,SUM(HT) as HT,SUM(TT) as TT,"
                 + "  SUM(BL) + SUM(HT) + SUM(TT) AS TONGTRGIAM, SUM(BL) + SUM(HT) + SUM(TT) + SUM(QT) AS TONGTR, "
                 + "  SUM(TRCL) as TRCL, SUM(TONGBIA) AS TONGBIA, sum(BIATRONGKY) as BIATRONGKY,SUM(TONGBIA) - "
                 + "  sum(BIATRONGKY) as BIAKYTRUOC,SUM(BIACAPLAI) AS BIACAPLAI, SUM(dauky) + sum(tangthamgia) -"
                 + "  sum(giamthamgia) AS CKTHAMGIA,sum(NGUOICHOTSOXN) as NGUOICHOTSOXN FROM ("
                 + "  SELECT makhoi, CKTHAMGIA as dauky,  "
                 + "  CKCAPSO  as daukycapso, CKTOROIHANGNAM as DauKyToRoiHangNam,CKTOROICHOTSO as DauKyToRoiChotSo,CKCAPLAISO as DauKyBiaCapLai,CKNGUOICHOTSOXN as DAUKYNGUOICHOTSOXN,"
                 + " 0 as tangthamgia ,0 as tangcapso,0 as giamthamgia,0 as giamcapso, 0 as QT, "
                 + "  0 as BL,0  as HT,0  as TT,0  as TRCL,0 AS TONGBIA,0 as TongBiaTang,0 AS BIACAPLAI,0 as BIATRONGKY,0 as NGUOICHOTSOXN  "
                 + "  FROM BC01 WHERE nam = '" + intNam + "' and thang = " + intThang;
            }
            queryString = queryString + "  union all " // tang tham gia, giam tham ga
                   + "  SELECT d.Makhoi,'' as dauky, '' as daukycapso,"
                   + "'' as DauKyToRoiHangNam,'' as DauKyToRoiChotSo,''as DauKyBiaCapLai,'' as DAUKYNGUOICHOTSOXN,"
                   + " ISNULL(SUM(CASE WHEN CHARINDEX(B.Pa,'TT/TU/TN/GN/DN/CD')=0 AND (ML_DK+HSL_DK)=0 AND (B.ML+B.HSL)<>0 THEN 1 ELSE 0 END), 0) as "
                   + "  tangthamgia, ''  as tangcapso, ISNULL(SUM(CASE WHEN CHARINDEX(B.Pa,'TT/TU/TN/GN/DN/CD')=0 AND (ML_DK+HSL_DK)<>0 AND (B.ML+B.HSL)=0 THEN 1 ELSE 0 END), 0) as giamthamgia,"
                   + "  '' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,'' AS BIACAPLAI,"
                   + "  '' as BIATRONGKY,'' as DAUKYNGUOICHOTSOXN FROM HSC47_Hdr a, HSC47_Dtl b,dmdonvi d  WHERE "
                   + "  a.KeySL= b.Keysl and a.madvi = d.madvi AND left(a.Madvi,1) <> 'B' AND LEFT(a.Maqt,6)=DENTHANG "
                   + "  AND A.NGAYCT >=  '" + strTuthang + "' AND A.NGAYCT <= '" + strDenthang + "'"
                   + "  group by d.Makhoi  "
                   + "  union all  " //so luong to roi theo qt,bl,ht,tt va to roi cap lai
                   + "  SELECT d.Makhoi,'' as dauky, '' as daukycapso,"
                   + "'' as DauKyToRoiHangNam,'' as DauKyToRoiChotSo,''as DauKyBiaCapLai,'' as DAUKYNGUOICHOTSOXN,"
                   + " '' as tangthamgia ,'' as tangcapso,'' as giamthamgia,'' as giamcapso, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN in ( 'QT','' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as QT, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN in ( 'BL','CS' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as BL, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'HT' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as HT, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAIXN = ( 'TU' ) and Intr_hdr.LOAI =  '1' then 1 else 0 end),0) as TT, "
                   + "  ISNULL(SUM(CASE WHEN  Intr_hdr.LOAI =  '2' then 1 else 0 end),0) as TRCL, '' AS TONGBIA,'' as TongBiaTang,"
                   + "  '' AS BIACAPLAI,'' as BIATRONGKY,'' as DAUKYNGUOICHOTSOXN FROM  " + Settings.Default.DBName_Sothe + ".dbo.INTR_HDR Intr_hdr, dmdonvi d"
                   + "  WHERE  "
                   + "  d.Madvi = Intr_hdr.Madvi AND left(Intr_hdr.Madvi,1) <> 'B' and Dain <> 0 "
                   + "  and NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "'"
                   + "  group by d.Makhoi  "
                   + "  UNION ALL " // tong bia
                   + "  SELECT d.makhoi,'' as dauky, '' as daukycapso,"
                   + "'' as DauKyToRoiHangNam,'' as DauKyToRoiChotSo,''as DauKyBiaCapLai,'' as DAUKYNGUOICHOTSOXN,"
                   + " '' as tangthamgia ,'' as tangcapso,'' as giamthamgia,"
                   + "  '' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL, "
                   + "  sum(case when Inso_dtl.LOAI = '1' then 1 else 0 end) AS TONGBIA,'' as TongBiaTang, '' AS BIACAPLAI, '' as BIATRONGKY,'' as DAUKYNGUOICHOTSOXN FROM MISBHXH_SOTHE_NT.dbo.INSO_DTL Inso_dtl, dmdonvi d "
                   + "  WHERE "
                   + "  d.Madvi = Inso_dtl.MADVI and left(Inso_dtl.Madvi,1) <> 'B'"
                   + "  and NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "'"
                   + "  group by d.makhoi  "
                   + "  UNION ALL " // bia cap lai
                   + "  SELECT c.makhoi,'' as dauky, '' as daukycapso,"
                   + "'' as DauKyToRoiHangNam,'' as DauKyToRoiChotSo,''as DauKyBiaCapLai,'' as DAUKYNGUOICHOTSOXN,"
                   + " '' as tangthamgia ,'' as tangcapso,'' as giamthamgia,"
                   + "  '' as giamcapso,'' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL, "
                   + "  '' AS TONGBIA,'' as TongBiaTang, count(a.sobhxh) AS BIACAPLAI, '' as BIATRONGKY,'' as DAUKYNGUOICHOTSOXN FROM HCHOSOCN a  LEFT JOIN HOSOCN b ON a.Sobhxh=b.Sobhxh LEFT JOIN DMDONVI c ON a.Madvi=c.Madvi  "
                   + "  WHERE left(a.Madvi,1) <> 'B' and a.Ngay BETWEEN '" + strTuthang + "' and '" + strDenthang + "'"
                   + "  group by c.makhoi  "
                   + " Union all "
                   + " select d.Makhoi,'' as dauky, '' as daukycapso,'' as DauKyToRoiHangNam,"
                   + " '' as DauKyToRoiChotSo,''as DauKyBiaCapLai,'' as DAUKYNGUOICHOTSOXN, '' as tangthamgia ,'' as tangcapso,"
                   + " '' as giamthamgia,'' as giamcapso,"
                   + " '' as QT, '' as BL,''  as HT,''  as TT,''  as TRCL,'' AS TONGBIA,'' as TongBiaTang,  "
                   + " '' AS BIACAPLAI,'' as BIATRONGKY  ,count(distinct SOBHXH) as NGUOICHOTSOXN"
                   + " FROM  MISBHXH_SOTHE_NT.dbo.INTR_HDR Intr_hdr, dmdonvi d  "
                   + " WHERE  d.Madvi = Intr_hdr.Madvi AND left(Intr_hdr.Madvi,1) <> 'B'  "
                   + " and NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "'"
                   + " group by d.Makhoi"
                   + "  ) a , Dmkhoi b where a.makhoi = b.makhoi  "
                   + "  group by a.makhoi,b.Tenkhoi ";

            queryString = queryString + strFootter;

            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {

                    newRow = dsBCCT.Tables[0].NewRow();
                    newRow["Madvi"] = strNamthang1;
                    newRow["TenDvi"] = reader["Tenkhoi"];
                    newRow["thang"] = reader["Makhoi"];
                                    
                    newRow["dauky"] = reader["dauky"];
                    newRow["daukycapso"] = Convert.ToInt32(reader["daukycapso"]) + Convert.ToInt32(reader["TONGBIA"]);
                    newRow["dkgiamso"] = Convert.ToInt32(reader["Daukytoroichotso"]) + Convert.ToInt32(reader["TONGTRGIAM"]);
                    newRow["dktoroichotso"] = Convert.ToInt32(reader["Daukytoroichotso"]) + Convert.ToInt32(reader["TONGTRGIAM"]);
                    newRow["dktoroihangnam"] =  Convert.ToInt32(reader["Daukytoroihangnam"]) + Convert.ToInt32(reader["QT"]);
                    newRow["dkcaplaiso"] = Convert.ToInt32(reader["Daukybiacaplai"]) + Convert.ToInt32(reader["BIACAPLAI"]);
                    newRow["dkNGUOICHOTSOXN"] = Convert.ToInt32(reader["DAUKYNGUOICHOTSOXN"]) + Convert.ToInt32(reader["NGUOICHOTSOXN"]);

                    newRow["tangthamgia"] = reader["tangthamgia"];
                    newRow["tangcapso"] = reader["tangcapso"];
                    newRow["giamthamgia"] = reader["giamthamgia"];
                    newRow["QT"] = reader["QT"];
                    newRow["BL"] = reader["BL"];
                    newRow["HT"] = reader["HT"];
                    newRow["TT"] = reader["TT"];
                    newRow["TONGTRGIAM"] = reader["TONGTRGIAM"];
                    newRow["TONGTR"] = reader["TONGTR"];
                    newRow["TRCL"] = reader["TRCL"];
                    newRow["TONGBIA"] = reader["TONGBIA"];
                    newRow["BIATRONGKY"] = reader["BIATRONGKY"];
                    newRow["BIAKYTRUOC"] = reader["BIAKYTRUOC"];
                    newRow["BIACAPLAI"] = reader["BIACAPLAI"];
                    newRow["ckthamgia"] = reader["ckthamgia"];
                    newRow["NGUOICHOTSOXN"] = reader["NGUOICHOTSOXN"];
                    
                    dsBCCT.Tables[0].Rows.Add(newRow);
                }
                reader.Close();
            }

            //lay tong so luong  quy
            newRow = dsBCCT.Tables[0].NewRow();
            newRow["dauky"] = 0; newRow["daukycapso"] = 0;
            newRow["dkgiamso"] = 0; newRow["dktoroichotso"] = 0;newRow["dktoroihangnam"] = 0;newRow["dkcaplaiso"] = 0;
            newRow["tangthamgia"] = 0;
            newRow["tangcapso"] = 0; newRow["giamthamgia"] = 0; newRow["QT"] = 0;
            newRow["BL"] = 0; newRow["HT"] = 0; newRow["TT"] = 0; newRow["TONGTRGIAM"] = 0;
            newRow["TONGTR"] = 0; newRow["TRCL"] = 0; newRow["TONGBIA"] = 0;
            newRow["BIATRONGKY"] = 0; newRow["BIAKYTRUOC"] = 0; newRow["BIACAPLAI"] = 0;
            newRow["ckthamgia"] = 0;
            newRow["NGUOICHOTSOXN"] = 0;
            newRow["dkNGUOICHOTSOXN"] = 0;
            using (SqlConnection connection =
                    new SqlConnection(connectionString))
            {

                SqlCommand command = connection.CreateCommand();
                connection.Open();
                command.CommandText = queryString;
                queryString = "delete " + strTable + " where nam = '" + txtNam.Text + "' and thang = " + Convert.ToInt16(dtThang.Value.Month);
                command.CommandText = queryString;
                command.ExecuteNonQuery();
                for (int i = 0; i < dsBCCT.Tables[0].Rows.Count; i++)
                {
                    newRow["Madvi"] = strNamthang1;
                    newRow["thang"] = "Tổng cộng";
                    newRow["dauky"] = Convert.ToInt32(newRow["dauky"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dauky"]);
                    newRow["daukycapso"] = Convert.ToInt32(newRow["daukycapso"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["daukycapso"]);
                    newRow["dkgiamso"] = Convert.ToInt32(newRow["dkgiamso"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dkgiamso"]);
                    newRow["dktoroichotso"] = Convert.ToInt32(newRow["dktoroichotso"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dktoroichotso"]);
                    newRow["dktoroihangnam"] = Convert.ToInt32(newRow["dktoroihangnam"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dktoroihangnam"]);
                    newRow["dkcaplaiso"] = Convert.ToInt32(newRow["dkcaplaiso"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dkcaplaiso"]);
                    newRow["dkNGUOICHOTSOXN"] = Convert.ToInt32(newRow["dkNGUOICHOTSOXN"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dkNGUOICHOTSOXN"]);

                    newRow["tangthamgia"] = Convert.ToInt32(newRow["tangthamgia"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["tangthamgia"]);
                   
                    newRow["giamthamgia"] = Convert.ToInt32(newRow["giamthamgia"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["giamthamgia"]);
                    newRow["QT"] = Convert.ToInt32(newRow["QT"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["QT"]);
                    newRow["BL"] = Convert.ToInt32(newRow["BL"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BL"]);
                    newRow["HT"] = Convert.ToInt32(newRow["HT"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["HT"]);
                    newRow["TT"] = Convert.ToInt32(newRow["TT"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TT"]);
                    newRow["TONGTRGIAM"] = Convert.ToInt32(newRow["TONGTRGIAM"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TONGTRGIAM"]);
                    newRow["TONGTR"] = Convert.ToInt32(newRow["TONGTR"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TONGTR"]);
                    newRow["TRCL"] = Convert.ToInt32(newRow["TRCL"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TRCL"]);
                    newRow["TONGBIA"] = Convert.ToInt32(newRow["TONGBIA"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["TONGBIA"]);
                    newRow["BIATRONGKY"] = Convert.ToInt32(newRow["BIATRONGKY"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BIATRONGKY"]);
                    newRow["BIAKYTRUOC"] = Convert.ToInt32(newRow["BIAKYTRUOC"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BIAKYTRUOC"]);
                    newRow["BIACAPLAI"] = Convert.ToInt32(newRow["BIACAPLAI"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["BIACAPLAI"]);
                    newRow["ckthamgia"] = Convert.ToInt32(newRow["ckthamgia"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["ckthamgia"]);
                    newRow["NGUOICHOTSOXN"] = Convert.ToInt32(newRow["NGUOICHOTSOXN"]) + Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["NGUOICHOTSOXN"]);
                   

                    //dua tong so luong quy vao table BC10SHB
                    queryString = "exec sp_executesql N'INSERT INTO " + strTable + " (MAKHOI,THANG,NAM,CKTHAMGIA,CKCAPSO,CKGIAMSO,CKTOROICHOTSO,CKTOROIHANGNAM,CKCAPLAISO,CKNGUOICHOTSOXN)"
                        + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)', N'@P1 char(2),@P2 tinyint,@P3 char(4),@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int', "
                        + " '" + dsBCCT.Tables[0].Rows[i]["thang"] + "', " + Convert.ToInt16(dtThang.Value.Month) + ", '" + dtThang.Value.Year + "', "
                        + dsBCCT.Tables[0].Rows[i]["ckthamgia"] + ", "
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["daukycapso"])) + ","
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dkgiamso"]) ) + ","
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dktoroichotso"]) ) + ","
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dktoroihangnam"])) + ","
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dkcaplaiso"])) + ","
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables[0].Rows[i]["dkNGUOICHOTSOXN"]));
                    command.CommandText = queryString;
                    command.ExecuteNonQuery();
                }
            }
            dsBCCT.Tables[0].Rows.Add(newRow); //add so tong so cua quy


            Cursor.Current = Cursors.Default;
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpBC01.rpt");
            cr.SetDataSource(dsBCCT);
            f.crpView.ReportSource = cr;
            f.Show();
        }

        private void btnBC2_Click(object sender, EventArgs e)
        {
            
            string queryString = null;
            int intNam = dtThang.Value.Year;
            int intThang = Convert.ToInt16(dtThang.Value.Month - 1);
            string strTuthang = Convert.ToInt16(dtThang.Value.Month-1) + "/26/" + dtThang.Value.Year;
            //string strDenthang = dtThang.Value.Month + "/" + DateTime.DaysInMonth(dtThang.Value.Year, dtThang.Value.Month) + "/" + dtThang.Value.Year;
            string strDenthang = dtThang.Value.Month + "/" + 25 + "/" + dtThang.Value.Year;
            DataRow newRow;
            bcctbhxh dsBCCT = new bcctbhxh();
            if (dtThang.Value.Month == 1)
            {
                intNam = intNam - 1;
                intThang = 12;
                strTuthang = "12/26/" + intNam;
            }
            Cursor.Current = Cursors.WaitCursor;
            if (dtThang.Value.Month == 1) // truong hop thang 1 tinh so luong dau ki nam
            {

                queryString = " select MADT,SUM(TheLuyKe) as TheLuyKe,Sum(TheTrongKy) as TheTrongKy,"
                 + " Sum(TheCapLaiLuyKe) as TheCapLaiLuyKe, sum(TheCapLaiTrongKy) as TheCapLaiTrongKy from ("
                 + " SELECT 'C¸c ®¬n vÞ, doanh nghiÖp' as MaDT,0 as TheLuyKe,"
                 + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe,"
                 + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1  then 1 else 0 end) as TheCapLaiTrongKy "
                 + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a "
                 + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi  "
                 + " left join DMKHOI d on b.MAKHOI = d.makhoi  "
                 + " WHERE  NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "' AND DENNGAY > '01/01/2012' AND Tinh_Den=''    "
                 + " and d.Makhoi not in ('BE','BN','BD','BO','BI') "
                 + " Union all "
                 + " SELECT d.tenkhoi as MaDT,0 as TheLuyKe,"
                 + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe,"
                 + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1  then 1 else 0 end) as TheCapLaiTrongKy"
                 + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a "
                 + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                 + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                 + " WHERE  NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "' AND Tinh_Den=''    "
                 + " and d.Makhoi = 'BE' "
                 + " GROUP BY d.tenkhoi "
                 + " Union all  "
                 + " SELECT 'NghÌo' as MaDT,0 as TheLuyKe, "
                 + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                 + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                 + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                 + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                 + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                 + " WHERE  NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "' AND Tinh_Den='' "
                 + " and (a.Madvi = 'BN0001A' OR  (RIGHT(LEFT(a.Madvi,6),3) >=29 AND RIGHT(LEFT(a.Madvi,6),3) <=54)) AND LEFT(a.Madvi,2) = 'BN'"
                 + " UNION ALL  "
                 + " SELECT 'CËn NghÌo' as MaDT,0 as TheLuyKe,  "
                 + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                 + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                 + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                 + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                 + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                 + " WHERE  NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "' AND Tinh_Den=''    "
                 + " and left(a.Madvi,2) = 'BN' AND  RIGHT(LEFT(a.Madvi,6),3) >=2 AND RIGHT(LEFT(a.Madvi,6),3) <=28 "
                 + " Union all  "
                 + " SELECT d.tenkhoi as MaDT,0 as TheLuyKe, "
                 + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                 + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                 + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                 + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                 + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                 + " WHERE  NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "' AND Tinh_Den=''    "
                 + " and d.Makhoi = 'BD' "
                 + " GROUP BY d.tenkhoi "
                 + " Union all  "
                 + " SELECT d.tenkhoi as MaDT,0 as TheLuyKe, "
                 + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                 + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                 + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                 + " left join MISBHXH_BHYT_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                 + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                 + " WHERE  NGAYCAP >= '" + strTuthang + "' and NGAYCAP <= '" + strDenthang + "' AND Tinh_Den=''    "
                 + " and d.Makhoi = 'BO' "
                 + " GROUP BY d.tenkhoi ) a "
                 + " group by MADT";

            }
            else //lay so luong dau ky tu table BC10SHB
            {
               
               queryString = " select MADT,SUM(TheLuyKe) as TheLuyKe,Sum(TheTrongKy) as TheTrongKy,"
                + " Sum(TheCapLaiLuyKe) as TheCapLaiLuyKe, sum(TheCapLaiTrongKy) as TheCapLaiTrongKy from ("
                + " SELECT Makhoi AS MADT,CKTHEMOI AS TheLuyKe,0 as TheTrongKy,"
                + " CKCAPLAI AS TheCapLaiLuyKe,0 as TheCapLaiTrongKy"
                + " FROM BC02 b "
                + " WHERE nam = '" + intNam + "' and thang = " + intThang
                + " union all" 
                + " SELECT 'C¸c ®¬n vÞ, doanh nghiÖp' as MaDT,0 as TheLuyKe,"
                + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe,"
                + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1  then 1 else 0 end) as TheCapLaiTrongKy "
                + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a "
                + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi  "
                + " left join DMKHOI d on b.MAKHOI = d.makhoi  "
                + " WHERE  Ngayin >= '" + strTuthang + "' and Ngayin <= '" + strDenthang + "' AND Tinh_Den='' and ngayin <> ''  "
                + " and d.Makhoi not in ('BE','BN','BD','BO','BI','AC')"
                + " Union all "
                + " SELECT d.tenkhoi as MaDT,0 as TheLuyKe,"
                + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe,"
                + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1  then 1 else 0 end) as TheCapLaiTrongKy"
                + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a "
                + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                + " WHERE  Ngayin >= '" + strTuthang + "' and Ngayin <= '" + strDenthang + "' AND Tinh_Den='' and ngayin <> ''  "
                + " and d.Makhoi = 'BE' "
                + " GROUP BY d.tenkhoi "
                + " Union all  "
                + " SELECT 'NghÌo' as MaDT,0 as TheLuyKe, "
                + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                + " WHERE  Ngayin >= '" + strTuthang + "' and Ngayin <= '" + strDenthang + "' AND Tinh_Den='' and ngayin <> ''     "
                + " and (a.Madvi = 'BN0001A' OR  (RIGHT(LEFT(a.Madvi,6),3) >=29 AND RIGHT(LEFT(a.Madvi,6),3) <=54)) AND LEFT(a.Madvi,2) = 'BN'"
                + " UNION ALL  "
                + " SELECT 'CËn NghÌo' as MaDT,0 as TheLuyKe,  "
                + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                + " WHERE  Ngayin >= '" + strTuthang + "' and Ngayin <= '" + strDenthang + "' AND Tinh_Den=''   and ngayin <> '' "
                + " and d.Makhoi = 'AC'"
                + " Union all  "
                + " SELECT d.tenkhoi as MaDT,0 as TheLuyKe, "
                + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                + " WHERE  Ngayin >= '" + strTuthang + "' and Ngayin <= '" + strDenthang + "' AND Tinh_Den='' and ngayin <> '' "
                + " and d.Makhoi = 'BD' "
                + " GROUP BY d.tenkhoi "
                + " Union all  "
                + " SELECT d.tenkhoi as MaDT,0 as TheLuyKe, "
                + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                + " left join MISBHXH_BHYT_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi "
                + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                + " WHERE  Ngayin >= '" + strTuthang + "' and Ngayin <= '" + strDenthang + "' AND Tinh_Den='' and ngayin <> '' "
                + " and d.Makhoi = 'BO' "
                + " GROUP BY d.tenkhoi "
                + " Union all  "
                + " SELECT d.tenkhoi as MaDT,0 as TheLuyKe, "
                + " SUM(CASE WHEN Lancap=1 THEN 1 ELSE 0 END) AS TheTrongKy,  0 as TheCapLaiLuyKe, "
                + " sum(case when a.Macl in (1,2,3,4) and LanCap <> 1 then 1 else 0 end) as TheCapLaiTrongKy "
                + " FROM MISBHXH_SOTHE_NT.dbo.INTHE_DTL a  "
                + " left join MISBHXH_nt.Dbo.DMDONVI b on a.Madvi = b.Madvi   "
                + " left join DMKHOI d on b.MAKHOI = d.makhoi   "
                + " WHERE  Ngayin >= '" + strTuthang + "' and Ngayin <= '" + strDenthang + "' AND Tinh_Den='' and ngayin <> '' "
                + " and d.Makhoi = 'BI' "
                + " GROUP BY d.tenkhoi ) a "
                + " group by MADT";
            }
           
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    newRow = dsBCCT.Tables["BCTHE"].NewRow();
                    newRow["thang"] = dtThang.Value.Year + "/" + string.Format("{0:00}", dtThang.Value.Month);
                    newRow["MAKHOI"] = reader["MADT"];
                    newRow["DAUKYTHEMOI"] = Convert.ToInt32(reader["TheLuyKe"]) + Convert.ToInt32(reader["TheTrongKy"]);
                    newRow["TRONGKYTHEMOI"] = reader["TheTrongKy"];
                    newRow["DAUKYCAPLAI"] = Convert.ToInt32(reader["TheCapLaiLuyKe"]) + Convert.ToInt32(reader["TheCapLaiTrongKy"]);
                    newRow["TRONGKYCAPLAI"] = reader["TheCapLaiTrongKy"];
                   
                    dsBCCT.Tables["BCTHE"].Rows.Add(newRow);
                }
                reader.Close();
            }

            //lay tong so luong  quy
            newRow = dsBCCT.Tables["BCTHE"].NewRow();
            newRow["DAUKYTHEMOI"] = 0;
            newRow["TRONGKYTHEMOI"] = 0;
            newRow["DAUKYCAPLAI"] = 0;
            newRow["TRONGKYCAPLAI"] = 0;
            using (SqlConnection connection =
                    new SqlConnection(connectionString))
            {

                SqlCommand command = connection.CreateCommand();
                connection.Open();
                command.CommandText = queryString;
                queryString = "delete BC02 where nam = '" + txtNam.Text + "' and thang = " + Convert.ToInt16(dtThang.Value.Month);
                command.CommandText = queryString;
                command.ExecuteNonQuery();
                for (int i = 0; i < dsBCCT.Tables["BCTHE"].Rows.Count; i++)
                {
                    newRow["thang"] = dtThang.Value.Year + "/" + string.Format("{0:00}", dtThang.Value.Month);
                    newRow["MAKHOI"] = "Tæng cæng";
                    newRow["DAUKYTHEMOI"] = Convert.ToInt32(newRow["DAUKYTHEMOI"]) + Convert.ToInt32(dsBCCT.Tables["BCTHE"].Rows[i]["DAUKYTHEMOI"]);
                    newRow["TRONGKYTHEMOI"] = Convert.ToInt32(newRow["TRONGKYTHEMOI"]) + Convert.ToInt32(dsBCCT.Tables["BCTHE"].Rows[i]["TRONGKYTHEMOI"]);
                    newRow["DAUKYCAPLAI"] = Convert.ToInt32(newRow["DAUKYCAPLAI"]) + Convert.ToInt32(dsBCCT.Tables["BCTHE"].Rows[i]["DAUKYCAPLAI"]);
                    newRow["TRONGKYCAPLAI"] = Convert.ToInt32(newRow["TRONGKYCAPLAI"]) + Convert.ToInt32(dsBCCT.Tables["BCTHE"].Rows[i]["TRONGKYCAPLAI"]);
                   
                    //dua tong so luong quy vao table BC10SHB
                    queryString = "exec sp_executesql N'INSERT INTO BC02(MAKHOI,THANG,NAM,CKTHEMOI,CKCAPLAI)"
                        + " VALUES (@P1,@P2,@P3,@P4,@P5)', N'@P1 char(100),@P2 tinyint,@P3 char(4),@P4 int,@P5 int', "
                        + " '" + dsBCCT.Tables["BCTHE"].Rows[i]["Makhoi"] + "', " + Convert.ToInt16(dtThang.Value.Month) + ", '" + dtThang.Value.Year + "', "
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables["BCTHE"].Rows[i]["DAUKYTHEMOI"])) + ","
                        + Convert.ToInt32(Convert.ToInt32(dsBCCT.Tables["BCTHE"].Rows[i]["DAUKYCAPLAI"]));
                    command.CommandText = queryString;
                    command.ExecuteNonQuery();
                }
            }
            dsBCCT.Tables["BCTHE"].Rows.Add(newRow); //add so tong so cua quy
            
            Cursor.Current = Cursors.Default;
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpBC02.rpt");
            cr.SetDataSource(dsBCCT);
            f.crpView.ReportSource = cr;
            f.Show();
        }

        private void cmdInNhan_Click(object sender, EventArgs e)
        {
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpInNhan.rpt");
            foreach (CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition thisParameterField in cr.DataDefinition.ParameterFields)
            {
                switch (thisParameterField.Name)
                {

                    case "dsnv":
                        thisParameterField.PromptText = cboNV.Text ;
                        thisParameterField.CurrentValues.Clear();
                        thisParameterField.CurrentValues.AddValue(cboNV.Text);
                        thisParameterField.ApplyCurrentValues(thisParameterField.CurrentValues);
                        break;
                    
                }
            }
            f.crpView.ReportSource = cr;
            f.Show();
        }

        private void btnDVI_Click(object sender, EventArgs e)
        {
            string queryString = "select * from dmdonvi where TATTOAN = 0 AND MAKHOI = 'TA' and (tendvi  LIKE '%TNHH%' or tendvi like '%Tr¸ch nhiÖm%')";
            dvi dviVO;
            List<dvi> lstDVI = new List<dvi>();
            List<dvi> lstKetQua = new List<dvi>();
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    dviVO = new dvi();
                    dviVO.Madvi = reader["Madvi"].ToString();
                    dviVO.Tendvi = reader["Tendvi"].ToString();
                    dviVO.Diachi = reader["Diachi"].ToString();
                    lstDVI.Add(dviVO);
                }
                reader.Close();
            }
            queryString = "select * from Northwind.dbo.dvi order by stt";
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    IEnumerable<dvi> items = from n in lstDVI
                                                 where n.Tendvi.ToLower().Contains(reader["tentat"].ToString().ToLower())
                                                 select n;
                    if (items.Count().ToString() == "0")
                    {
                        dviVO = new dvi();
                        dviVO.Tendvi = "";
                        dviVO.Diachi = "";
                        dviVO.Stt = Convert.ToInt32(reader["stt"]);
                        lstKetQua.Add(dviVO);
                    }
                    else
                    {
                        foreach (var item in items.ToList())
                        {
                            dviVO = new dvi();
                            dviVO.Madvi = item.Madvi;
                            dviVO.Tendvi = item.Tendvi;
                            dviVO.Diachi = item.Diachi;
                            dviVO.Stt = Convert.ToInt32(reader["stt"]);
                            lstKetQua.Add(dviVO);
                        }
                    }
                }
                reader.Close();
            }
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpDMDONVI.rpt");
            cr.SetDataSource(lstKetQua);
            f.crpView.ReportSource = cr;
            f.Show();

        }

        private void btnSotam_Click(object sender, EventArgs e)
        {
            Random rnd = new Random();
            string queryString = "select * from CAPSONN where NAM = " + (Convert.ToInt32(txtNamTam.Text) - 1);
            clsCapso capsoVO;
            List<clsCapso> lstCapso = new List<clsCapso>();
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    capsoVO = new clsCapso();
                    capsoVO.Madvi = reader["Madvi"].ToString();
                    capsoVO.KeySL = ModuleBHXH.CreateRunninNo(rnd);
                    capsoVO.Tuso = Convert.ToInt64(reader["tuso"]);
                    capsoVO.Denso = Convert.ToInt64(reader["denso"]);
                    capsoVO.Soluong = Convert.ToInt32(reader["soluong"]);
                    lstCapso.Add(capsoVO);
                }
                reader.Close();
                foreach (clsCapso item in lstCapso)
                {
                    queryString = "INSERT INTO dbo.CAPSONN (NGAY,MADVI,NAM,SOLUONG,TUSO,"
                    + " DENSO,USERCODE,MA_QUAN,GHICHU,KEYSL) VALUES ('"
                    + txtNamTam.Text + "/01/01','" + item.Madvi + "','" + txtNamTam.Text + "'"
                    + "," + item.Soluong + ",'" + item.Tuso + "','" + item.Denso + "','" + ModuleBHXH.strUser + "',"
                    + "'01','','" + item.KeySL + "')";
                    command.CommandText = queryString;
                    command.ExecuteNonQuery();
                }

            }
        }

        private void btnC06_Click(object sender, EventArgs e)
        {
            string queryString = "select ngay,sum(capmoi) as capmoi,sum(mat) as mat , sum(hong) as hong,"
            + " sum(hoten + ngaysinh) as thongtin,sum(doiso) as doiso,sum(inhong) as inhong,"
            + " sum(capmoi + mat + hong + hoten + ngaysinh + doiso + inhong) as tong from dbo.S04" + txtThangnam.Text + "$"  
            + " group by ngay "
            + " order by ngay ";
            C06_C07 dsC06 = new C06_C07();
            DataRow newRow;
            int intsodauky = Convert.ToInt32(txtSodauky.Text);
            string strConnection = "Data Source=tqhuy-pc;Initial Catalog=misbhxh_nt;User ID=sa;Password=s@";
            using (SqlConnection connection =
                   new SqlConnection(strConnection))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    newRow = dsC06.Tables[0].NewRow();
                    newRow["ngay"] = reader["Ngay"];
                    newRow["nhanvien"] = ModuleBHXH.strUser;
                    newRow["capmoi"] = reader["capmoi"];
                    newRow["hong"] = reader["hong"];
                    newRow["mat"] = reader["mat"];
                    newRow["thongtin"] = reader["thongtin"];
                    newRow["doiso"] = reader["doiso"];
                    newRow["inhong"] = reader["inhong"];
                    newRow["tong"] = reader["tong"];
                    newRow["Sodau"] = intsodauky;
                    newRow["Socuoi"] = intsodauky - Convert.ToInt32(newRow["tong"]);
                    intsodauky = Convert.ToInt32(newRow["Socuoi"]);
                    dsC06.Tables[0].Rows.Add(newRow);
                }
                reader.Close();
            }
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpC06.rpt");
            cr.SetDataSource(dsC06);
            f.crpView.ReportSource = cr;
            f.Show();

        }

        private void btnC07_Click(object sender, EventArgs e)
        {
            string queryString = "SELECT NGAY, SUM(ISNULL(CAPMOI,0) + ISNULL(MAT,0) + ISNULL(HONG,0) + "
            + " ISNULL(SAITHONGTIN,0) + ISNULL(DOINOIKHAM,0) + ISNULL(DOIQUYENLOI,0) + "
            + " ISNULL(INHONG,0)) AS SUDUNG,"
            + " SUM(ISNULL(CAPMOI,0) - ISNULL(giahan,0)) CAPMOI, SUM(ISNULL(giahan,0)) AS GIAHAN,"
            + " SUM(ISNULL(MAT,0)) AS CAPLAI,SUM(ISNULL(HONG,0)) AS HONG,SUM(ISNULL(SAITHONGTIN,0)) AS SAITHONGTIN, "
            + " SUM(ISNULL(DOINOIKHAM,0)) AS DOINOIKHAM , SUM(ISNULL(DOIQUYENLOI,0)) AS DOIQUYENLOI,SUM(ISNULL(INHONG,0)) AS INHONG "
            + " FROM S06" + txtThangnam.Text + "$"  
            + " GROUP BY ngay "
            + " ORDER BY ngay ";
            C06_C07 dsC07 = new C06_C07();
            DataRow newRow;
            int intsodauky = Convert.ToInt32(txtThedauky.Text);
            string strConnection = "Data Source=tqhuy-pc;Initial Catalog=misbhxh_nt;User ID=sa;Password=s@";
            using (SqlConnection connection =
                   new SqlConnection(strConnection))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    newRow = dsC07.Tables[1].NewRow();
                    newRow["ngay"] = reader["Ngay"];
                    newRow["nhanvien"] = ModuleBHXH.strUser;
                    newRow["tong"] = reader["sudung"];
                    newRow["capmoi"] = reader["capmoi"];
                    newRow["giahan"] = reader["giahan"];
                    newRow["mat"] = reader["caplai"];
                    newRow["hong"] = reader["hong"];
                    newRow["thongtin"] = reader["saithongtin"];
                    newRow["doinoikham"] = reader["doinoikham"];
                    newRow["doiquyenloi"] = reader["doiquyenloi"];
                    newRow["inhong"] = reader["inhong"];
                    newRow["Sodau"] = intsodauky;
                    newRow["Socuoi"] = intsodauky - Convert.ToInt32(newRow["tong"]);
                    intsodauky = Convert.ToInt32(newRow["Socuoi"]);
                    dsC07.Tables[1].Rows.Add(newRow);
                }
                reader.Close();
            }
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpC07.rpt");
            cr.SetDataSource(dsC07);
            f.crpView.ReportSource = cr;
            f.Show();
        }

        private void btndsC13_Click(object sender, EventArgs e)
        {
            string queryString = "select a.madvi,b.tendvi,b.diachi as dcdonvi,rtrim(a.ho) + ' ' + a.ten as hoten, a.ngaysinh,c.gioitinh,a.diachi,a.sobhxh"
            + " from dbo.INTR_HDR a inner join MISBHXH_NT.dbo.dmdonvi b "
            + " on a.madvi = b.madvi "
            + " inner join MISBHXH_NT.dbo.hosocn c on c.sobhxh = a.sobhxh "
            + " where ngaycap = '08/20/2013' and a.usercode = 'TQHUY' and ngayin is null "
            + " AND THANGKT = '201212' and DTDONVI = '201212'"
            + " order by a.sobhxh ";
            danhsachthe dsbhyt = new danhsachthe();
            DataRow newRow;
            string strConnection = "Data Source=pc001;Initial Catalog=misbhxh_sothe_nt;User ID=sa;Password=s@";
            using (SqlConnection connection =
                   new SqlConnection(strConnection))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandTimeout = 300;
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    newRow = dsbhyt.Tables[0].NewRow();
                    newRow["TenDVI"] = reader["TenDVI"];
                    newRow["Madvi"] = reader["Madvi"];
                    newRow["DCCty"] = reader["dcdonvi"];
                    newRow["Sobhxh"] = reader["Sobhxh"];
                    newRow["Hoten"] = reader["Hoten"].ToString().Trim();
                    newRow["NgaySinh"] = reader["NgaySinh"];
                    if (reader["GioiTinh"].ToString() == "0")
                    {
                        newRow["GioiTinh"] = "X";
                    }
                    else
                    {
                        newRow["GioiTinh"] = "";
                    }
                    newRow["DiaChi"] = reader["DiaChi"];
                    
                    dsbhyt.Tables[0].Rows.Add(newRow);
                }
                reader.Close();
            }
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpListC13.rpt");
            cr.SetDataSource(dsbhyt);
            f.crpView.ReportSource = cr;
            f.Show();
        }

        private void btnSolieu_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            DeleteTable();
            importDataFromExcel();
            GetdataToFromChuyenQuan();
            //FormatGrid();
            Cursor.Current = Cursors.Default;
            MessageBox.Show("Import d÷ liÖu thµnh c«ng");
        }
        private void GetdataToFromNo()
        {
             SqlDataAdapter myAdapter;
             myAdapter = new SqlDataAdapter();
             DataSet ds = new DataSet();

            string strConnection = "Data Source=pc001;Initial Catalog=Northwind;User ID=sa;Password=s@";
            string sql = "SELECT g.stt,g.madvi,sum(unc.tien_vnd) as tien,b.DSNV FROM chuyenquan g "
            + " left join MISBHXH_NT.dbo.UNC unc on unc.madvi = g.madvi and  month(Ngay)= " + dtThangno.Value.Month
            + " and year(Ngay) = " + dtThangno.Value.Year 
            + " and  MaNH LIKE '%' "
            + " left join MISBHXH_NT.dbo.dmdonvi b on g.madvi = b.madvi "
            + " group by g.madvi,g.stt,b.dsnv "
            + " order by g.stt "
            + " ";
            using (SqlConnection connection =
                   new SqlConnection(strConnection))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                connection.Open();
                command.ExecuteNonQuery();
                myAdapter.SelectCommand = command;
                myAdapter.Fill(ds);
                fa.DataSource = ds.Tables[0];
            }
        }
        private void GetdataToFromChuyenQuan()
        {
            SqlDataAdapter myAdapter;
            myAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            string strConnection = "Data Source=pc001;Initial Catalog=Northwind;User ID=sa;Password=s@";
            string sql = "select a.stt,a.madvi,b.tendvi,b.DSNV from chuyenquan a left join "
                + " MISBHXH_NT.dbo.dmdonvi b "
                + " on a.madvi = b.madvi "
                + " order by a.stt ";
          
            using (SqlConnection connection =
                   new SqlConnection(strConnection))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                connection.Open();
                command.ExecuteNonQuery();
                myAdapter.SelectCommand = command;
                myAdapter.Fill(ds);
                fa.DataSource = ds.Tables[0];
            }
        }
        private void DeleteTable()
        {
            string strConnection = "Data Source=pc001;Initial Catalog=Northwind;User ID=sa;Password=s@";
            string sql = "delete chuyenquan";
            using (SqlConnection connection =
                   new SqlConnection(strConnection))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                connection.Open();
                command.ExecuteNonQuery();
            }
            
        }
        public void importDataFromExcel()
        {
            string FileName = System.IO.Directory.GetCurrentDirectory().ToString() + "\\Imports.xls";
            // Connection String to Excel Workbook,Replace DataSource value to point to your excel file location
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FileName + " ;Extended Properties=Excel 8.0";

            // Create Connection to Excel Workbook
            using (OleDbConnection connection =
                         new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand
                        ("Select * FROM [Sheet1$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=pc001;Initial Catalog=Northwind;User ID=sa;Password=s@"; 

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "CHUYENQUAN";
                        bulkCopy.WriteToServer(dr);

                    }
                }
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            // choose file
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.DefaultExt = "xls";
            dlg.FileName = "*.xls";
            if (dlg.ShowDialog() != DialogResult.OK)
                return;
            // save grid as sheet in the book
            FileFlags flags = FileFlags.None;
            fa.SaveGrid(dlg.FileName, FileFormatEnum.Excel, flags);
        }

        private void btnNo_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            DeleteTable();
            importDataFromExcel();
            GetdataToFromNo();
            //FormatGrid();
            Cursor.Current = Cursors.Default;
            MessageBox.Show("Import d÷ liÖu thµnh c«ng");
        }
    }
}
